Technologia True Cache w bazach Oracle AI Database 26ai


192.168.1.200   srv01.localdomain srv01
192.168.1.201   srv02.localdomain srv02


sql / as sysdba

archive log list

cd $ORACLE_HOME/dbs

scp orapwdb01 srv02.localdomain:/u01/app/Oracle

sql sys@srv01:1521/DB01 as sysdba

SELECT HOST_NAME FROM V$INSTANCE;

cd $ORACLE_HOME/bin

./dbca -silent -createTrueCache \
-gdbName DB01 \
-sourceDBConnectionString  srv01:1521/DB01 \
-passwordFileFromSourceDB /u01/app/oracle/orapwdb01 \
-dbUniqueName DB01_TC \
-sid db01


cd $ORACLE_HOME/network/admin

vi listener.ora

VALID_NODE_CHECKING_REGISTRATION_LISTENER = ON
REGISTRATION_INVITED_NODES_LISTENER = (srv01.localdomain,srv02.localdomain)


lsnrctl stop

lsnrctl start

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

BEGIN
   DBMS_SERVICE.CREATE_SERVICE('HRSRV', 'HRSRV');
   DBMS_SERVICE.START_SERVICE('HRSRV');
END;
/

SELECT service_id, name, true_cache_service FROM V$ACTIVE_SERVICES WHERE name='HRSRV';


sql sys@srv02:1521/DB01_TC as sysdba

SELECT HOST_NAME FROM V$INSTANCE;

cd $ORACLE_HOME/bin

./dbca -configureDatabase \
-configureTrueCacheInstanceService \
-sourceDB db01 \
-trueCacheConnectString srv02:1521/DB01_TC \
-trueCacheServiceName HR_TC \
-serviceName HRSRV \
-pdbName PDB1 \
-silent


lsnrctl status


sql / as sysdba

SELECT SERVICE_ID, NAME, TRUE_CACHE_SERVICE FROM V$ACTIVE_SERVICES WHERE NAME='HRSRV';

sql / as sysdba

SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

show pdbs

SELECT DEST_ID, TRUE_CACHE_NAME, PRIMARY_NAME, STATUS, REMOTE_VERSION FROM V$TRUE_CACHE;

SELECT DEST_ID, TRUE_CACHE_NAME, PRIMARY_NAME, STATUS, REMOTE_VERSION FROM V$TRUE_CACHE;

sql / as sysdba

SHUTDOWN

STARTUP




Technologia AI Vector Search 

mkdir -p /u01/app/oracle/models/onnx

cd /u01/app/oracle/models/onnx

wget -O all_MiniLM_L12_v2.onnx \
"https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/iPX9W0MZeRkwJKWdFmdJCemmN-iKAl_bFvNGYLW7YqIrw4kKsukL24J2q93Beb9S/n/adwc4pm/b/OML-ai-models/o/all_MiniLM_L12_v2.onnx"

pwd

ls -l

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE USER VSEARCHUSER IDENTIFIED BY Password_1 QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION, DB_DEVELOPER_ROLE, CREATE MINING MODEL TO VSEARCHUSER;

CREATE OR REPLACE DIRECTORY VSEARCH_DIR AS '/u01/app/oracle/models/onnx';

GRANT READ, WRITE ON DIRECTORY VSEARCH_DIR TO VSEARCHUSER;


sql VSEARCHUSER@//localhost:1521/pdb1

BEGIN
  DBMS_VECTOR.DROP_ONNX_MODEL(
    model_name => 'ALL_MINILM_L12_V2',
    force      => TRUE);

  DBMS_VECTOR.LOAD_ONNX_MODEL(
    directory  => 'VSEARCH_DIR',
    file_name  => 'all_MiniLM_L12_v2.onnx',
    model_name => 'ALL_MINILM_L12_V2');
END;
/

SELECT MODEL_NAME, ALGORITHM, MINING_FUNCTION FROM USER_MINING_MODELS WHERE  MODEL_NAME = 'ALL_MINILM_L12_V2';

DESC VSEARCHUSER.BBC_NEWS;

ALTER TABLE VSEARCHUSER.BBC_NEWS ADD (
  TITLE_VECTOR VECTOR
);


DESC VSEARCHUSER.BBC_NEWS;

UPDATE VSEARCHUSER.BBC_NEWS SET TITLE_VECTOR = VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING TITLE AS DATA);

COMMIT;

VARIABLE SEARCH_TEXT VARCHAR2(100);

EXEC :SEARCH_TEXT := 'Climate news from Europe';

SELECT vector_distance(TITLE_VECTOR, (vector_embedding(all_minilm_l12_v2 using :search_text as data))) as distance,
       TITLE
FROM   VSEARCHUSER.BBC_NEWS
order by 1
fetch approximate first 40 rows only;


EXEC :SEARCH_TEXT := 'How to spend the summer months?';

SELECT vector_distance(TITLE_VECTOR, (vector_embedding(all_minilm_l12_v2 using :search_text as data))) as distance,
       TITLE
FROM   VSEARCHUSER.BBC_NEWS
order by 1
fetch approximate first 40 rows only;




Automatyczne wycofywanie transakcji

ALTER SESSION SET TXN_PRIORITY = LOW;

ALTER SESSION SET CONTAINER=PDB1;

CREATE TABLE HR.PRIORITYTEST (
TEST NUMBER(10) NOT NULL
);

INSERT INTO HR.PRIORITYTEST (TEST) VALUES (1);

COMMIT;

SELECT * FROM HR.PRIORITYTEST;

ALTER SYSTEM SET PRIORITY_TXNS_HIGH_WAIT_TARGET=10 SCOPE=BOTH;

ALTER SYSTEM SET PRIORITY_TXNS_MEDIUM_WAIT_TARGET=10 SCOPE=BOTH;

ALTER SYSTEM SET priority_txns_mode = ROLLBACK SCOPE=BOTH;

SHOW PARAMETER PRIORITY_TXNS

ALTER SESSION SET CONTAINER=PDB1;

ALTER SESSION SET TXN_PRIORITY = LOW;

UPDATE HR.PRIORITYTEST SET TEST=100;

ALTER SESSION SET CONTAINER=PDB1;

ALTER SESSION SET TXN_PRIORITY = LOW;

UPDATE HR.PRIORITYTEST SET TEST=200;

ALTER SESSION SET CONTAINER=PDB1;

UPDATE HR.PRIORITYTEST SET TEST=1000;

SELECT * FROM HR.PRIORITYTEST;




Tabele do 4096 kolumn

SHOW PARAMETERS max_columns

ALTER SYSTEM SET max_columns=EXTENDED SCOPE=SPFILE;

SHUTDOWN IMMEDIATE

STARTUP

SHOW PARAMETERS max_columns


DECLARE
  ddl CLOB := 'CREATE TABLE TABLECOL (';
  c   INTEGER;
BEGIN
  FOR i IN 1..4096 LOOP
    ddl := ddl || 'C' || LPAD(i, 4, '0') || ' NUMBER'
              || CASE WHEN i < 4096 THEN ',' END;
  END LOOP;

  ddl := ddl || ')';

  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, ddl, DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/


SELECT COUNT(*) FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = 'TABLECOL';




Zarządzanie kolejnością otwierania baz PDB

sql / as sysdba


show pdbs

ALTER PLUGGABLE DATABASE PDB1 PRIORITY 10;
Pluggable database PDB1, PRIORITY altered.


ALTER PLUGGABLE DATABASE PDB2 PRIORITY 20;
Pluggable database PDB2, PRIORITY altered.


ALTER PLUGGABLE DATABASE PDB1 SAVE STATE;
Pluggable database PDB1 altered.


ALTER PLUGGABLE DATABASE PDB2 SAVE STATE;
Pluggable database PDB2 altered.

SHUTDOWN IMMEDIATE

STARTUP

SELECT OPEN_TIME, NAME, PRIORITY FROM V$PDBS ORDER BY 1;




Pakiet DBMS_DICTIONARY_CHECK

sql / as sysdba

SET SERVEROUTPUT ON SIZE UNLIMITED
EXECUTE DBMS_DICTIONARY_CHECK.FULL;


SET SERVEROUTPUT ON SIZE UNLIMITED
EXECUTE DBMS_DICTIONARY_CHECK.CRITICAL;


EXECUTE dbms_dictionary_check.full(repair=>TRUE);




Rola DB_DEVELOPER_ROLE


SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'RESOURCE';

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'RESOURCE';

SELECT PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE = 'RESOURCE';

SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'CONNECT';

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'CONNECT';

SELECT PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE = 'CONNECT';

SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DB_DEVELOPER_ROLE';

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'DB_DEVELOPER_ROLE';

SELECT PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DB_DEVELOPER_ROLE';




Użytkownicy READ ONLY

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE USER DEV_USER IDENTIFIED BY Password_1 QUOTA UNLIMITED ON USERS;

GRANT DB_DEVELOPER_ROLE TO DEV_USER;

SELECT USERNAME, READ_ONLY FROM DBA_USERS WHERE USERNAME = 'DEV_USER';

sql DEV_USER@//localhost:1521/PDB1

CREATE TABLE TEST_TABLE_READ_ONLY_USERS (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(50)
);

INSERT INTO TEST_TABLE_READ_ONLY_USERS VALUES (1, 'Dane testowe 1');

INSERT INTO TEST_TABLE_READ_ONLY_USERS VALUES (2, 'Dane testowe 2');

INSERT INTO TEST_TABLE_READ_ONLY_USERS VALUES (3, 'Dane testowe 3');

COMMIT;

SELECT * FROM TEST_TABLE_READ_ONLY_USERS;

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

ALTER USER DEV_USER READ ONLY;

SELECT USERNAME, READ_ONLY FROM DBA_USERS WHERE USERNAME = 'DEV_USER';

sql DEV_USER@//localhost:1521/pdb1

SELECT * FROM TEST_TABLE_READ_ONLY_USERS;

INSERT INTO TEST_TABLE_READ_ONLY_USERS VALUES (4, 'Dane testowe 4');




Uprawnienia nadawane do schematu

GRANT SELECT ANY TABLE ON SCHEMA HR TO ANNA;



Nowe funkcje w SQL*Plus 

SELECT * FROM NIEISTNIEJACA_TABELA;

SET ERRORDETAILS VERBOSE

SET ERRORDETAILS VERBOSE

SELECT * FROM NIEISTNIEJACA_TABELA;

PING DB01

SHOW CONNECTION NETSERVICENAMES




Instrukcja SELECT bez klauzuli FROM

SELECT SYSDATE FROM DUAL;
